package com.yss.sofa.report.engine.cell.invisible.dataset;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.yss.sofa.report.engine.ConnectionFactory;
import com.yss.sofa.report.engine.Const;
import com.yss.sofa.report.engine.Context;
import com.yss.sofa.report.engine.DataSet;
import com.yss.sofa.report.engine.DataSetData;
import com.yss.sofa.report.engine.cell.ReportCell;
import com.yss.sofa.report.engine.cell.invisible.InvisibleCell;
import com.yss.sofa.report.engine.util.RenderUtil;

public class SqlDataSetCell extends InvisibleCell implements DataSet{
	private static final long serialVersionUID = -672639061640295096L;
	private static final Logger LOGGER=LoggerFactory.getLogger(SqlDataSetCell.class);
	private String db;
	private String sql;
	private boolean pre;
	private String exec;
	private Set<String> params=new HashSet<String>();
	protected void init() {
		super.init();
		this.type=Const.REPORT_CELL_TYPE_SQL_DATASET;
		this.pre=true;
		this.exec="true";
	}
	public void assignTo(ReportCell cell){
		SqlDataSetCell _=(SqlDataSetCell)cell;
		super.assignTo(_);
		_.db=this.db;
		_.sql=this.sql;
		_.pre=this.pre;
		_.exec=this.exec;
		_.params.clear();
		_.params.addAll(this.params);
	}
	public SqlDataSetCell create(){
		SqlDataSetCell _=new SqlDataSetCell();
		this.assignTo(_);
		return _;
	}
	private String renderSql(String sql,Map<String,Serializable> paramsmap,Map<String,String> consts) throws Exception{
		Map<String,Object> values=new HashMap<String, Object>();
		for(String key:params){
			values.put(key, paramsmap.get(key));
		}
		values.put("CONSTS", consts);
		return RenderUtil.renderString(sql, values);
	}
	public DataSetData execute(String classname,Context context,Map<String,Serializable> paramsmap,Map<String,String> consts) throws Exception{
		String s=sql;
		if(pre){
			try {
				s=renderSql(sql, paramsmap, consts);
			} catch (Exception e) {
				throw new Exception("SQL数据集["+code+"]的SQL渲染失败",e);
			}
		}
		long begintime=System.currentTimeMillis();
		ConnectionFactory factory=context.get(Context.CONTEXT_KEY_CONNECTION_FACTORY, ConnectionFactory.class);
		Connection connection;
		try {
			connection = factory.getConnection(db);
			//connection.setAutoCommit(true);
		} catch (Exception e) {
			throw new Exception("SQL数据集["+code+"]无法连接到数据库["+db+"]", e);
		}
		PreparedStatement ps=null;
		ResultSet resultSet=null;
		try {
			ps=connection.prepareStatement(s);
			DataSetData result=new DataSetData();
			resultSet=ps.executeQuery();
			ResultSetMetaData meta=resultSet.getMetaData();
			int colCount=meta.getColumnCount();
			for(int i=0;i<colCount;i++){
				String field=meta.getColumnName(i+1).toLowerCase();
				result.addField(field);
			}
			while(resultSet.next()){
				Map<String,String> record=new HashMap<String, String>();
				for(int i=0;i<colCount;i++){
					record.put(meta.getColumnName(i+1).toLowerCase(), resultSet.getString(i+1));
				}
				result.addRecord(record);
			}
			long endtime=System.currentTimeMillis();
			if(LOGGER.isDebugEnabled()){
				LOGGER.debug("在数据库["+db+"]上执行SQL：\n"+s+"\n的耗时为"+(endtime-begintime)/1000+"秒");
			}
			if(result.size()>10000){
				LOGGER.warn("SQL数据集["+code+"]返回的数据已超过10000条");
			}
			return result.convert();
		} catch (SQLException e) {
			throw new Exception("SQL数据集["+code+"]无法在数据库["+db+"]上执行SQL：\n"+s+"\n", e);
		} finally {
			if(resultSet!=null) resultSet.close();
			if(ps!=null) ps.close();
			connection.close();
		}
	}
	public String getDb() {
		return db;
	}
	public void setDb(String db) {
		this.db = db;
	}
	public String getSql() {
		return sql;
	}
	public void setSql(String sql) {
		this.sql = sql;
	}
	public Set<String> getParams() {
		return params;
	}
	public void setParams(Set<String> params) {
		this.params = params;
	}
	public boolean isPre() {
		return pre;
	}
	public void setPre(boolean pre) {
		this.pre = pre;
	}
	public String getExec() {
		return exec;
	}
	public void setExec(String exec) {
		this.exec = exec;
	}

}
